package org.truenewx.tnxjee.repo.jdbc.util;

import java.lang.reflect.Method;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import java.util.function.BiConsumer;
import java.util.function.Function;
import javax.sql.DataSource;

import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.BeanUtils;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.jdbc.support.MetaDataAccessException;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.jdbc.support.rowset.SqlRowSetMetaData;
import org.truenewx.tnxjee.core.Strings;
import org.truenewx.tnxjee.core.util.*;
import org.truenewx.tnxjee.repo.jdbc.exception.JdbcIllegalCharException;
import org.truenewx.tnxjee.repo.jdbc.exception.JdbcNonExistentUserException;

/**
 * JDBC工具类
 *
 * @author jianglei
 */
public class JdbcUtil {

    private static final String CREATE_TABLE_SQL_PREFIX = "create table ";
    private static final String CREATE_VIEW_SQL_PREFIX = "create view ";
    private static final String ILLEGAL_CHARS = "'; -/*";

    private JdbcUtil() {
    }

    public static String getSchema(DatabaseMetaData metaData) throws SQLException {
        String jdbcUrl = metaData.getURL();
        if (StringUtils.isNotBlank(jdbcUrl)) {
            if (isH2(metaData)) {
                int index = jdbcUrl.lastIndexOf(Strings.COLON);
                return jdbcUrl.substring(index + 1);
            }
            int index1 = jdbcUrl.indexOf(NetUtil.PROTOCOL_MARK) + NetUtil.PROTOCOL_MARK.length();
            int index2 = jdbcUrl.indexOf(Strings.QUESTION, index1);
            if (index2 < 0) {
                index2 = jdbcUrl.length();
            }
            String schema = jdbcUrl.substring(index1, index2);
            int index3 = schema.indexOf(Strings.SLASH);
            if (index3 >= 0) {
                return schema.substring(index3 + 1);
            }
        }
        return null;
    }

    public static String getSchema(DataSource dataSource) {
        try {
            return JdbcUtils.extractDatabaseMetaData(dataSource, JdbcUtil::getSchema);
        } catch (MetaDataAccessException e) {
            LogUtil.error(JdbcUtil.class, e);
        }
        return null;
    }

    public static boolean existsSchema(DataSource dataSource, String schema) {
        if (dataSource != null && StringUtils.isNotBlank(schema)) {
            try {
                return JdbcUtils.extractDatabaseMetaData(dataSource, metaData -> {
                    if (isMySql(metaData)) {
                        ResultSet rs = metaData.getCatalogs();
                        while (rs.next()) {
                            String value = rs.getString("TABLE_CAT");
                            if (value.equalsIgnoreCase(schema)) {
                                return true;
                            }
                        }
                        rs.close();
                    } else {
                        ResultSet rs = metaData.getSchemas();
                        while (rs.next()) {
                            String value = rs.getString("TABLE_SCHEM");
                            if (value.equalsIgnoreCase(schema)) {
                                return true;
                            }
                        }
                        rs.close();
                    }
                    return false;
                });
            } catch (MetaDataAccessException e) {
                LogUtil.error(JdbcUtil.class, e);
            }
        }
        return false;
    }

    public static boolean isH2(DatabaseMetaData metaData) throws SQLException {
        return "h2".equalsIgnoreCase(metaData.getDatabaseProductName());
    }

    public static boolean isMySql(DatabaseMetaData metaData) throws SQLException {
        return "mysql".equalsIgnoreCase(metaData.getDatabaseProductName());
    }

    public static boolean isReadOnly(DataSource dataSource) throws MetaDataAccessException {
        return JdbcUtils.extractDatabaseMetaData(dataSource, DatabaseMetaData::isReadOnly);
    }

    public static void createSchema(DataSource dataSource, String schema) {
        if (dataSource != null && StringUtils.isNotBlank(schema)) {
            JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
            jdbcTemplate.execute("create database if not exists " + schema);
        }
    }

    public static Collection<String> getExistingTableNames(DataSource dataSource, boolean withSchema,
            String... tableNamePatterns) {
        return getExistingTableOrViewNames(dataSource, withSchema, tableNamePatterns, true);
    }

    public static Collection<String> getExistingViewNames(DataSource dataSource, boolean withSchema,
            String... viewNamePatterns) {
        return getExistingTableOrViewNames(dataSource, withSchema, viewNamePatterns, false);
    }

    private static List<String> getExistingTableOrViewNames(DataSource dataSource, boolean withSchema,
            String[] tableNamePatterns, boolean actual) {
        if (dataSource != null) {
            try {
                return JdbcUtils.extractDatabaseMetaData(dataSource, metaData -> {
                    List<String> tables = new ArrayList<>();
                    String schema = getSchema(metaData);
                    ResultSet rs = getTablesOrViews(metaData, schema, Strings.PERCENT, actual);
                    while (rs.next()) {
                        String tableName = rs.getString("TABLE_NAME");
                        if (ArrayUtils.isEmpty(tableNamePatterns) || StringUtil.wildcardMatchOneOf(tableName,
                                tableNamePatterns)) {
                            if (withSchema && StringUtils.isNotBlank(schema)) {
                                tableName = schema + Strings.DOT + tableName;
                            }
                            tables.add(tableName);
                        }
                    }
                    rs.close();
                    return tables;
                });
            } catch (MetaDataAccessException e) {
                LogUtil.error(JdbcUtil.class, e);
            }
        }
        return Collections.emptyList();
    }

    private static ResultSet getTablesOrViews(DatabaseMetaData metaData, String schema, String tableNamePattern,
            boolean actual) throws SQLException {
        String catalog = null;
        String schemaPattern = null;
        if (isMySql(metaData)) { // MySQL获取表名时通过catalog区分schema
            catalog = schema;
        } else if (isH2(metaData)) { // H2的表名需为大写
            tableNamePattern = tableNamePattern.toUpperCase();
        } else {
            schemaPattern = schema;
        }
        return metaData.getTables(catalog, schemaPattern, tableNamePattern, new String[]{actual ? "TABLE" : "VIEW"});
    }

    public static boolean existsTable(DataSource dataSource, String schema, String tableName) {
        return existsTableOrView(dataSource, schema, tableName, true);
    }

    public static boolean existsView(DataSource dataSource, String schema, String tableName) {
        return existsTableOrView(dataSource, schema, tableName, false);
    }

    private static boolean existsTableOrView(DataSource dataSource, String schema, String tableName, boolean actual) {
        try {
            return JdbcUtils.extractDatabaseMetaData(dataSource,
                    metaData -> existsTableOrView(metaData, schema, tableName, actual));
        } catch (MetaDataAccessException e) {
            LogUtil.error(JdbcUtil.class, e);
        }
        return false;
    }

    public static boolean existsTableOrView(DatabaseMetaData metaData, String schema, String tableName, boolean actual)
            throws SQLException {
        ResultSet rs = getTablesOrViews(metaData, schema == null ? getSchema(metaData) : schema, tableName, actual);
        boolean exists = rs.next();
        rs.close();
        return exists;
    }

    public static JdbcTableColumn getTableColumn(DatabaseMetaData metaData, String tableName, String columnName)
            throws SQLException {
        JdbcTableColumn column = new JdbcTableColumn(columnName);
        if (JdbcUtil.isH2(metaData)) {
            tableName = tableName.toUpperCase();
            columnName = columnName.toUpperCase();
        }
        ResultSet rs = metaData.getColumns(null, null, tableName, columnName);
        if (rs.next()) {
            column.setAutoIncrement(getBoolean(rs.getString("IS_AUTOINCREMENT")));
            column.setNullable(getBoolean(rs.getString("IS_NULLABLE")));
            column.setLength(rs.getInt("COLUMN_SIZE"));
            column.setDataType(rs.getInt("DATA_TYPE"));
            column.setPrecision(rs.getInt("COLUMN_SIZE"));
            column.setScale(rs.getInt("DECIMAL_DIGITS"));
            return column;
        }
        return null;
    }

    private static Boolean getBoolean(String value) {
        if ("yes".equalsIgnoreCase(value)) {
            return true;
        } else if ("no".equalsIgnoreCase(value)) {
            return false;
        } else {
            return null;
        }
    }

    public static void copyTableStructure(DataSource originalDataSource, String originalSchema,
            DataSource destDataSource, String destSchema, Collection<String> tableNames) {
        copyStructure(originalDataSource, originalSchema, destDataSource, destSchema, tableNames, true, null);
    }

    public static void copyViews(DataSource originalDataSource, String originalSchema,
            DataSource destDataSource, String destSchema, Collection<String> viewNames,
            Function<String, String> mapper) {
        copyStructure(originalDataSource, originalSchema, destDataSource, destSchema, viewNames, false, mapper);
    }

    private static void copyStructure(DataSource originalDataSource, String originalSchema, DataSource destDataSource,
            String destSchema, Collection<String> tableNames, boolean actual, Function<String, String> mapper) {
        if (CollectionUtils.isNotEmpty(tableNames)) {
            JdbcTemplate originalTemplate = new JdbcTemplate(originalDataSource);
            JdbcTemplate destTemplate = new JdbcTemplate(destDataSource);
            destTemplate.execute("set foreign_key_checks = 0");
            String sqlPrefix = actual ? CREATE_TABLE_SQL_PREFIX : CREATE_VIEW_SQL_PREFIX;
            for (String tableName : tableNames) {
                if (!existsTableOrView(destDataSource, destSchema, tableName, actual)) {
                    String sql = "show " + sqlPrefix + originalSchema + Strings.DOT + tableName;
                    Map<String, Object> result = originalTemplate.queryForMap(sql);
                    for (Object value : result.values()) {
                        if (value instanceof String) {
                            String s = (String) value;
                            s = s.toLowerCase();
                            if (s.startsWith(sqlPrefix)) {
                                int index = s.indexOf(Strings.SPACE, sqlPrefix.length());
                                sql = sqlPrefix + destSchema + Strings.DOT + tableName + s.substring(index);
                                // 忽略最后一个)后的内容
                                sql = sql.substring(0, sql.lastIndexOf(Strings.RIGHT_BRACKET) + 1);
                                break;
                            } else if (!actual && s.startsWith("create ")) { // 视图的创建语句可能存在多余的字符
                                sql = s.replaceFirst("create .* view ", sqlPrefix);
                                if (mapper != null) {
                                    sql = mapper.apply(sql);
                                }
                                break;
                            }
                        }
                    }
                    destTemplate.execute(sql);
                }
            }
            destTemplate.execute("set foreign_key_checks = 1");
        }
    }

    public static boolean copyTableStructure(DataSource originalDataSource, DataSource destDataSource,
            List<String> tableNames) {
        String originalSchema = getSchema(originalDataSource);
        String destSchema = getSchema(destDataSource);
        // 从数据源中可能取不到schema
        if (StringUtils.isNotBlank(originalSchema) && StringUtils.isNotBlank(destSchema)) {
            copyTableStructure(originalDataSource, originalSchema, destDataSource, destSchema, tableNames);
            return true;
        }
        return false;
    }

    public static void copyTableData(DataSource originalDataSource, DataSource destDataSource,
            Collection<String> tableNames, boolean clearDestTable) {
        if (CollectionUtils.isNotEmpty(tableNames)) {
            JdbcTemplate originalTemplate = new JdbcTemplate(originalDataSource);
            JdbcTemplate destTemplate = new JdbcTemplate(destDataSource);
            for (String tableName : tableNames) {
                if (!isIllegal(tableName) && existsTable(originalDataSource, null, tableName)
                        && existsTable(destDataSource, null, tableName)) {
                    if (clearDestTable) {
                        destTemplate.execute("delete from " + tableName);
                    }
                    SqlRowSet rs = originalTemplate.queryForRowSet("select * from " + tableName);
                    SqlRowSetMetaData metaData = rs.getMetaData();
                    int columnCount = metaData.getColumnCount();
                    if (columnCount > 0) {
                        StringBuilder sql = new StringBuilder("insert into ").append(tableName)
                                .append(Strings.LEFT_BRACKET);
                        for (int i = 1; i <= columnCount; i++) { // metaData.getColumnName()索引从1开始计数
                            sql.append(metaData.getColumnName(i)).append(Strings.COMMA);
                        }
                        sql.deleteCharAt(sql.length() - 1);
                        sql.append(") values (");
                        sql.append(String.join(Strings.COMMA, Collections.nCopies(columnCount, Strings.QUESTION)));
                        sql.append(Strings.RIGHT_BRACKET);
                        while (rs.next()) {
                            Object[] params = new Object[columnCount];
                            for (int i = 0; i < columnCount; i++) {
                                params[i] = rs.getObject(i + 1);
                            }
                            destTemplate.update(sql.toString(), params);
                        }
                    }
                }
            }
        }
    }

    public static String getUrl(DataSource template) {
        try {
            return JdbcUtils.extractDatabaseMetaData(template, DatabaseMetaData::getURL);
        } catch (MetaDataAccessException e) {
            throw new RuntimeException(e);
        }
    }

    private static String insertSchema(String url, String schema) {
        int startIndex = url.indexOf(NetUtil.PROTOCOL_MARK) + NetUtil.PROTOCOL_MARK.length();
        int slashIndex = url.indexOf(Strings.SLASH, startIndex);
        int questionIndex = url.indexOf(Strings.QUESTION, startIndex);
        String prefix;
        String suffix = questionIndex < 0 ? Strings.EMPTY : url.substring(questionIndex);
        if (slashIndex < 0) { // 原本没有schema
            if (questionIndex < 0) { // 没有附加参数
                prefix = url;
            } else { // 有附加参数
                prefix = url.substring(0, questionIndex);
            }
        } else { // 原本有schema
            prefix = url.substring(0, slashIndex);
        }
        return prefix + Strings.SLASH + schema + suffix;
    }

    @SuppressWarnings("unchecked")
    public static <T extends DataSource> T buildDataSource(T template, String schema, BiConsumer<T, String> urlSetter) {
        T dataSource = (T) ClassUtil.newInstance(template.getClass());
        BeanUtils.copyProperties(template, dataSource);
        String templateUrl = getUrl(template);
        String url = insertSchema(templateUrl, schema);
        if (urlSetter == null) {
            urlSetter = JdbcUtil::setUrlToDataSource;
        }
        urlSetter.accept(dataSource, url);
        return dataSource;
    }

    private static void setUrlToDataSource(DataSource dataSource, String url) {
        if (!BeanUtil.setPropertyValue(dataSource, "url", url)) {
            if (!BeanUtil.setPropertyValue(dataSource, "URL", url)) {
                throw new RuntimeException("The url property cannot be set to the " + dataSource.getClass());
            }
        }
    }

    @SuppressWarnings("unchecked")
    public static <T extends DataSource> T buildDataSource(T template, String host, int port, String schema,
            BiConsumer<T, String> urlSetter) {
        T dataSource = (T) ClassUtil.newInstance(template.getClass());
        BeanUtils.copyProperties(template, dataSource);

        String templateUrl = getUrl(template);
        // 先取没有schema和参数的部分
        String url = templateUrl.substring(0,
                templateUrl.indexOf(NetUtil.PROTOCOL_MARK) + NetUtil.PROTOCOL_MARK.length())
                + host + Strings.COLON + port;
        if (StringUtils.isNotBlank(schema)) {
            int questionIndex = url.indexOf(Strings.QUESTION);
            url += Strings.SLASH + schema + (questionIndex >= 0 ? url.substring(questionIndex) : Strings.EMPTY);
        }
        if (urlSetter == null) {
            urlSetter = JdbcUtil::setUrlToDataSource;
        }
        urlSetter.accept(dataSource, url);
        return dataSource;
    }

    public static void closeDataSource(DataSource dataSource) {
        try {
            if (dataSource instanceof AutoCloseable) {
                ((AutoCloseable) dataSource).close();
            } else {
                try {
                    Method method = dataSource.getClass().getMethod("close");
                    method.invoke(dataSource);
                } catch (Exception ignored) {
                }
            }
        } catch (Exception e) {
            LogUtil.error(JdbcUtil.class, e);
        }
    }

    public static void updateUserPassword(DataSource dataSource, String host, String username, String password) {
        if (isIllegal(host)) {
            throw new JdbcIllegalCharException("host", ILLEGAL_CHARS);
        }
        if (isIllegal(username)) {
            throw new JdbcIllegalCharException("username", ILLEGAL_CHARS);
        }
        if (isIllegal(password)) {
            throw new JdbcIllegalCharException("password", ILLEGAL_CHARS);
        }
        try {
            JdbcUtils.extractDatabaseMetaData(dataSource, metaData -> {
                if (isMySql(metaData)) {
                    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
                    SqlRowSet rs = jdbcTemplate.queryForRowSet(
                            "select count(*) from mysql.user where user=? and host=?", username, host);
                    rs.next();
                    if (rs.getInt(1) == 0) {
                        throw new JdbcNonExistentUserException(host, username);
                    }
                    String sql = "alter user '%s'@'%s' identified by '%s'";
                    sql = String.format(sql, username, host, password);
                    jdbcTemplate.execute(sql);
                    return true;
                }
                return false;
            });
        } catch (MetaDataAccessException e) {
            throw new RuntimeException(e);
        }
    }

    private static boolean isIllegal(String s) {
        return StringUtils.containsAny(s, ILLEGAL_CHARS);
    }

    public static boolean existsIndex(DataSource dataSource, String schema, String tableName, String indexName) {
        try {
            return JdbcUtils.extractDatabaseMetaData(dataSource, metaData -> {
                String catalog = null;
                String s = null;
                String table = tableName;
                if (isMySql(metaData)) { // MySQL获取表名时通过catalog区分schema
                    catalog = schema;
                } else if (isH2(metaData)) { // H2的表名需为大写
                    table = tableName.toUpperCase();
                } else {
                    s = schema;
                }
                try (ResultSet rs = metaData.getIndexInfo(catalog, s, table, false, false)) {
                    while (rs.next()) {
                        String index = rs.getString("INDEX_NAME");
                        if (index != null && index.equalsIgnoreCase(indexName)) {
                            return true;
                        }
                    }
                }
                return false;
            });
        } catch (MetaDataAccessException e) {
            LogUtil.error(JdbcUtil.class, e);
        }
        return false;
    }

}
